/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package rw.ac.kiosc.jdbc.school.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import rw.ac.kiosc.jdbc.school.dao.UserDAO;
import rw.ac.kiosc.jdbc.school.orm.User;

/**
 *
 * @author antoine
 */
public class UserDAOImpl implements UserDAO {

    private Connection connection;
    public static UserDAOImpl instance;

    public static UserDAOImpl getInstance(Connection connection) {
        if (instance == null) {
            instance = new UserDAOImpl(connection);
        }
        return instance;
    }

    private UserDAOImpl() {
    }

    private UserDAOImpl(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public User saveUser(User user) {
        User ret = null;
        try {
            Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statement.executeUpdate("INSERT INTO user(username, hashed_password, name) values(\'" + user.getUsername() + "\', \'" + user.getHashedPassword() + "\', \'" + user.getName() + "\')");
            ResultSet usersList = statement.executeQuery("SELECT * FROM user WHERE username=\'" + user.getUsername() + "\'");
            if (usersList.next()) {
                ret = new User(usersList.getInt("user_id"), usersList.getString("username"), usersList.getString("hashed_password"), usersList.getString("name"));
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public User updateUser(User user) {
        User ret = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statementOne.executeUpdate("UPDATE user SET username=\'" + user.getUsername() + "\', hashed_password=\'" + user.getHashedPassword() + "\', name=\'" + user.getName() + "\'");
            ResultSet usersList = statementTwo.executeQuery("SELECT * FROM user WHERE username=\'" + user.getUsername() + "\'");
            if (usersList.next()) {
                ret = new User(usersList.getInt("user_id"), usersList.getString("username"), usersList.getString("hashed_password"), usersList.getString("name"));
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public User saveOrUpdateUser(User user) {
        if (user.getUserId() > 0) {
            try {
                Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet usersList = statement.executeQuery("SELECT user_id FROM user WHERE user_id=" + user.getUserId());
                if (usersList.next()) {
                    return updateUser(user);
                } else {
                    return saveUser(user);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
                return null;
            }
        } else {
            return saveUser(user);
        }
    }

    public boolean deleteUser(User user) {
        try {
            Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            int n = statement.executeUpdate("DELETE FROM user WHERE user_id=" + user.getUserId());
            return (n == 1) ? true : false;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public User getUserById(int userId) {
        User user = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet usersList = statementOne.executeQuery("SELECT * FROM user WHERE user_id=" + userId);
            if (usersList.next()) {
                user = new User(usersList.getInt("user_id"), usersList.getString("username"), usersList.getString("hashed_password"), usersList.getString("name"));
            }
            return user;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return user;
        }
    }

    public List<User> getAllUsers() {
        List<User> users = new ArrayList<User>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet usersList = statementOne.executeQuery("SELECT * FROM user");
            while (usersList.next()) {
                User user = new User(usersList.getInt("user_id"), usersList.getString("username"), usersList.getString("hashed_password"), usersList.getString("name"));
                users.add(user);
            }
            return users;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return users;
        }
    }

    public User getUserByUsername(String username) {
        User user = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet usersList = statementOne.executeQuery("SELECT * FROM user WHERE username=\'" + username + "\'");
            if (usersList.next()) {
                user = new User(usersList.getInt("user_id"), usersList.getString("username"), usersList.getString("hashed_password"), usersList.getString("name"));
            }
            return user;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return user;
        }

    }

    public List<User> getUsersByName(String name) {
        List<User> users = new ArrayList<User>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet usersList = statementOne.executeQuery("SELECT * FROM user WHERE name LIKE \'" + name + "\'");
            while (usersList.next()) {
                User user = new User(usersList.getInt("user_id"), usersList.getString("username"), usersList.getString("hashed_password"), usersList.getString("name"));
                users.add(user);
            }
            return users;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return users;
        }
    }
}
